const connection = require('../app/database')

class MomentService {
  // 插入数据到数据库
  async create(...payload) {
    // 1.获取请求参数
    const [content, user_id] = payload

    // 2.定义预处理语句
    const statement = 'INSERT INTO `moment` ( `content`, `user_id`) VALUES (?, ?);'

    // 3.执行sql语句
    let [res, fields] = await connection.execute(statement, [content, user_id])
    return res
  }

  async queryList(...payload) {
    // 1.获取请求参数
    const [offset=0, size=10] = payload

    // 2.定义预处理语句
    const statement = `
      SELECT 
        m.id id, m.content content,m.createTime createTime,m.updateTime updateTime,
        JSON_OBJECT('id',u.id,'name',u.name) user,
        ( SELECT COUNT(*) FROM comment WHERE comment.moment_id = m.id ) commentCounts,
        ( SELECT COUNT(*) FROM moment_lable ml WHERE ml.moment_id = m.id ) lableCounts
      FROM moment m
      LEFT JOIN user u ON m.user_id = u.id
      LIMIT ? OFFSET ?
    `

    // 3.执行sql语句
    let [res, fields] = await connection.execute(statement, [size, offset])
    return res
  }

  async queryDetailById(...payload) {
    // 1.获取请求参数
    const [momentId] = payload

    // 2.定义预处理语句
    const statement = `
        SELECT 
        m.id id, m.content content,m.createTime createTime,m.updateTime updateTime,
        JSON_OBJECT('id',u.id,'name',u.name) user,
        (
          SELECT
            JSON_ARRAYAGG(JSON_OBJECT(
              'id', c.id, 'content', c.content, 'commentId', c.comment_id,
              'user', JSON_OBJECT('id', cu.id, 'name', cu.name)
            ))
          FROM comment c
          LEFT JOIN user cu ON cu.id = c.user_id
          WHERE c.moment_id = m.id
        ) comments,
        (
          JSON_ARRAYAGG(JSON_OBJECT(
            'id', l.id, 'name', l.name
          ))
        ) lables
      FROM moment m
      LEFT JOIN user u ON m.user_id = u.id
      LEFT JOIN moment_lable ml ON ml.moment_id = m.id
      LEFT JOIN lable l ON l.id = ml.lable_id
      WHERE m.id = ?
      GROUP BY m.id
    `

    // 3.执行sql语句
    let [res, fields] = await connection.execute(statement, [momentId])
    return res
  }

  async updateContentById(...payload) {
    // 1.获取请求参数
    const [momentId,content] = payload

    // 2.定义预处理语句
    const statement = `UPDATE moment SET content = ? WHERE id = ?;`

    // 3.执行sql语句
    let [res, fields] = await connection.execute(statement, [content,momentId])
    return res
  }

  async deleteContentById(...payload) {
    // 1.获取请求参数
    const [momentId] = payload

    // 2.定义预处理语句
    const statement = `DELETE FROM moment WHERE id = ?;`

    // 3.执行sql语句
    let [res, fields] = await connection.execute(statement, [momentId])
    return res
  }

  /**
   * 检查新数据插入关系表中是否重复
   * @param  {...any} payload 
   * @returns 
   */
  async checkMomentLableExists(...payload) {
    // 1.获取请求参数
    const [momentId,lableId] = payload

    // 2.定义预处理语句
    const statement = 'SELECT * FROM `moment_lable` WHERE moment_id = ? AND lable_id = ?;'

    // 3.执行sql语句
    let [res, fields] = await connection.execute(statement, [momentId,lableId])
    return !!res.length
  }

  async addCommentAndLablesRelation(...payload) {
    // 1.获取请求参数
    const [momentId,lableId] = payload

    // 2.定义预处理语句
    const statement = 'INSERT INTO `moment_lable` ( `moment_id`, `lable_id`) VALUES (?, ?);'

    // 3.执行sql语句
    let [res, fields] = await connection.execute(statement, [momentId,lableId])
    return res
  }
}

module.exports = new MomentService()